use anyhow::Result;
use serde::{Deserialize, Serialize};
use sqlx::{Pool, Postgres, MySql, Sqlite};
use tracing::{info, error};

use super::types::DatabaseType;

/// Database connection configuration
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct ConnectionConfig {
    pub name: String,
    pub database_type: DatabaseType,
    pub host: String,
    pub port: u16,
    pub database: String,
    pub username: String,
    pub password: String,
    pub ssl_mode: SslMode,
    pub connection_timeout: u64,
    pub max_connections: u32,
}



/// SSL connection modes
#[derive(Debug, Clone, Serialize, Deserialize)]
pub enum SslMode {
    Disable,
    Allow,
    Prefer,
    Require,
}

/// Database connection wrapper
#[derive(Debug)]
pub enum DatabaseConnection {
    PostgreSQL(Pool<Postgres>),
    MySQL(Pool<MySql>),
    SQLite(Pool<Sqlite>),
    // TODO: Add MongoDB and Redis connections
}

impl DatabaseConnection {
    /// Create a new database connection
    pub async fn new(config: ConnectionConfig) -> Result<Self> {
        info!("Creating database connection: {}", config.name);
        
        match config.database_type {
            DatabaseType::PostgreSQL => {
                let connection_string = format!(
                    "postgresql://{}:{}@{}:{}/{}",
                    config.username, config.password, config.host, config.port, config.database
                );
                let pool = sqlx::postgres::PgPoolOptions::new()
                    .max_connections(config.max_connections)
                    .connect(&connection_string)
                    .await?;
                Ok(DatabaseConnection::PostgreSQL(pool))
            }
            DatabaseType::MySQL => {
                let connection_string = format!(
                    "mysql://{}:{}@{}:{}/{}",
                    config.username, config.password, config.host, config.port, config.database
                );
                let pool = sqlx::mysql::MySqlPoolOptions::new()
                    .max_connections(config.max_connections)
                    .connect(&connection_string)
                    .await?;
                Ok(DatabaseConnection::MySQL(pool))
            }
            DatabaseType::SQLite => {
                let pool = sqlx::sqlite::SqlitePoolOptions::new()
                    .max_connections(config.max_connections)
                    .connect(&config.database)
                    .await?;
                Ok(DatabaseConnection::SQLite(pool))
            }
            _ => {
                error!("Database type not yet implemented: {:?}", config.database_type);
                Err(anyhow::anyhow!("Database type not yet implemented"))
            }
        }
    }
    
    /// Test a database connection
    pub async fn test(config: &ConnectionConfig) -> Result<()> {
        info!("Testing database connection");

        match config.database_type {
            DatabaseType::PostgreSQL => {
                let connection_string = format!(
                    "postgresql://{}:{}@{}:{}/{}",
                    config.username, config.password, config.host, config.port, config.database
                );
                let pool = sqlx::postgres::PgPoolOptions::new()
                    .max_connections(1)
                    .connect(&connection_string)
                    .await?;

                // Test with a simple query
                sqlx::query("SELECT 1").execute(&pool).await?;
                pool.close().await;
            }
            DatabaseType::MySQL => {
                let connection_string = format!(
                    "mysql://{}:{}@{}:{}/{}",
                    config.username, config.password, config.host, config.port, config.database
                );
                let pool = sqlx::mysql::MySqlPoolOptions::new()
                    .max_connections(1)
                    .connect(&connection_string)
                    .await?;

                // Test with a simple query
                sqlx::query("SELECT 1").execute(&pool).await?;
                pool.close().await;
            }
            DatabaseType::SQLite => {
                let pool = sqlx::sqlite::SqlitePoolOptions::new()
                    .max_connections(1)
                    .connect(&config.database)
                    .await?;

                // Test with a simple query
                sqlx::query("SELECT 1").execute(&pool).await?;
                pool.close().await;
            }
            _ => {
                return Err(anyhow::anyhow!("Database type not yet implemented for testing"));
            }
        }

        Ok(())
    }
    
    /// Close the database connection
    pub async fn close(&mut self) -> Result<()> {
        info!("Closing database connection");
        
        match self {
            DatabaseConnection::PostgreSQL(pool) => {
                pool.close().await;
            }
            DatabaseConnection::MySQL(pool) => {
                pool.close().await;
            }
            DatabaseConnection::SQLite(pool) => {
                pool.close().await;
            }
        }
        
        Ok(())
    }
    
    /// Get connection type
    pub fn connection_type(&self) -> DatabaseType {
        match self {
            DatabaseConnection::PostgreSQL(_) => DatabaseType::PostgreSQL,
            DatabaseConnection::MySQL(_) => DatabaseType::MySQL,
            DatabaseConnection::SQLite(_) => DatabaseType::SQLite,
        }
    }
}
